﻿using Common;
using IDal;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Autofac;
using Autofac.Integration.Mvc;
using Dapper;
using DapperExtensions;


namespace Dal
{
    public class BaseDal<T> : IBaseDal<T> where T : class, new()
    {
        public IDbConnection Con { get { return AutofacDependencyResolver.Current.ApplicationContainer.Resolve<IDbConnection>(); } }//每次获取一个手动注入的Con

        /// <summary>
        /// 增加一条记录
        /// </summary>
        /// <param name="entity">实体</param>
        public void Add(T entity)
        {
            using (var con=Con)//这种写法，少触发一次get
            {
                con.Insert<T>(entity);
            }
        }

        /// <summary>
        /// 批量增加
        /// </summary>
        /// <param name="entityList">实体集合</param>
        public void Add(List<T> entityList)
        {
            using (var con=Con)
            {
                con.Insert<T>(entityList);
            }
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>操作成功与否</returns>
        public bool Delete(T entity)
        {
            bool result = false;

            using (var con=Con)
            {
                result = con.Delete<T>(entity);
            }

            return result;
        }

        /// <summary>
        /// 条件删除（DapperExtensions）
        /// </summary>
        /// <param name="predicate">条件</param>
        /// <returns>操作成功与否</returns>
        public bool Delete(IPredicate predicate)
        {
            bool result = false;

            using (var con=Con)
            {
                result = con.Delete<T>(predicate);
            }

            return result;
        }

        /// <summary>
        /// 更新一条记录
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns>操作成功与否</returns>
        public bool Update(T entity)
        {
            bool result = false;

            using (var con=Con)
            {
                result = con.Update<T>(entity);
            }

            return result;
        }

        /// <summary>
        /// 主键查询
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>实体</returns>
        public T Get(object id)
        {
            T result = null;

            using (var con=Con)
            {
                result = con.Get<T>(id);
            }

            return result;
        }

        /// <summary>
        /// 带参数的sql查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">实体参数</param>
        /// <returns>实体</returns>
        public T Get(string sql, T parameter)
        {
            T result = null;

            using (var con=Con)
            {
                result = con.Query<T>(sql, parameter).SingleOrDefault();
            }

            return result;
        }

        /// <summary>
        /// 条件查询（DapperExtensions）
        /// </summary>
        /// <param name="predicate">条件</param>
        /// <returns>实体</returns>
        public T Get(IPredicate predicate)
        {
            T result = null;

            using (var con=Con)
            {
                result = con.GetList<T>(predicate).SingleOrDefault();
            }

            return result;
        }

        /// <summary>
        /// sql查询记录数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>记录数</returns>
        public int GetCount(string sql)
        {
            int result = 0;

            using (var con=Con)
            {
                result = con.Query<int>(sql).SingleOrDefault();
            }

            return result;
        }

        /// <summary>
        /// 带参数的sql查询记录数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">实体参数</param>
        /// <returns>记录数</returns>
        public int GetCount(string sql, T parameter)
        {
            int result = 0;

            using (var con=Con)
            {
                result = con.Query<int>(sql,parameter).SingleOrDefault();
            }

            return result;
        }

        /// <summary>
        /// 条件查询记录数（DapperExtensions）
        /// </summary>
        /// <param name="predicate">条件</param>
        /// <returns>记录数</returns>
        public int GetCount(IPredicate predicate)
        {
            int result = 0;

            using (var con=Con)
            {
                result = con.Count<T>(predicate);
            }

            return result;
        }

        /// <summary>
        /// 查询所有记录
        /// </summary>
        /// <returns>集合</returns>
        public List<T> GetList()
        {
            List<T> result = null;

            using (var con=Con)
            {
                result = con.GetList<T>().ToList();
            }

            return result;
        }

        /// <summary>
        /// sql查询集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>集合</returns>
        public List<T> GetList(string sql)
        {
            List<T> result = null;

            using (var con=Con)
            {
                result = con.Query<T>(sql).ToList();
            }

            return result;
        }

        /// <summary>
        /// 带参数的sql查询集合
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">实体参数</param>
        /// <returns>集合</returns>
        public List<T> GetList(string sql, T parameter)
        {
            List<T> result = null;

            using (var con=Con)
            {
                result = con.Query<T>(sql,parameter).ToList();
            }

            return result;
        }

        /// <summary>
        /// 条件查询集合（DapperExtensions）
        /// </summary>
        /// <param name="predicate">条件</param>
        /// <param name="sort">排序</param>
        /// <returns>集合</returns>
        public List<T> GetList(IPredicate predicate, IList<ISort> sort = null)
        {
            List<T> result = null;

            using (var con=Con)
            {
                result = con.GetList<T>(predicate,sort).ToList();
            }

            return result;
        }

        /// <summary>
        /// sql分页查询
        /// </summary>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="total">总记录数</param>
        /// <param name="sql">sql语句</param>
        /// <param name="orderBy">排序</param>
        /// <returns>集合</returns>
        public List<T> GetListByPage(int pageSize, int pageIndex, out int total, string sql,string orderBy)
        {
            List<T> result = null;
            total = GetCount("select count(*) " + sql.Substring(sql.IndexOf("from")));
            string query = "select top " + pageSize + " o.* from (" +
                               "select row_number() over(order by " + orderBy + ") as rownumber,* from(" +sql+") as oo" +
                           ") as o where rownumber>" + (pageIndex - 1) * pageSize;

            using (var con=Con)
            {
                result = con.Query<T>(query).ToList();
            }

            return result;
        }

        /// <summary>
        /// 带参数的sql分页查询
        /// </summary>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="total">总记录数</param>
        /// <param name="sql">sql语句</param>
        /// <param name="parameter">实体参数</param>
        /// <param name="orderBy">排序</param>
        /// <returns>集合</returns>
        public List<T> GetListByPage(int pageSize, int pageIndex, out int total, string sql, T parameter, string orderBy)
        {
            List<T> result = null;
            total = GetCount("select count(*) " + sql.Substring(sql.IndexOf("from")),parameter);
            string query = "select top " + pageSize + " o.* from (" +
                               "select row_number() over(order by " + orderBy + ") as rownumber,* from(" + sql + ") as oo" +
                           ") as o where rownumber>" + (pageIndex - 1) * pageSize;

            using (var con=Con)
            {
                result = con.Query<T>(query,parameter).ToList();
            }

            return result;
        }

        /// <summary>
        /// 条件分页查询（DapperExtensions）
        /// </summary>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="total">总记录数</param>
        /// <param name="predicate">条件</param>
        /// <param name="sort">排序</param>
        /// <returns>集合</returns>
        public List<T> GetListByPage(int pageSize, int pageIndex, out int total, IPredicate predicate, IList<ISort> sort)
        {
            List<T> result = null;
            total = GetCount(predicate);

            using (var con=Con)
            {
                result = con.GetPage<T>(predicate,sort,pageIndex-1,pageSize).ToList();
            }

            return result;
        }

    }
}
